package com.nf.dao.impl;

import com.nf.dao.TicketInfoDao;
import com.nf.entity.TicketInfo;
import com.nf.util.DBUtil;
import com.nf.vo.FilmIdVo;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TicketInfoDaoImpl implements TicketInfoDao {
    @Override
    public int add(TicketInfo ticket) {
        int r=0;

        Connection conn= DBUtil.getConnection();
        String sql="insert into ticketInfo values(?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,ticket.getFilmName());
            pstmt.setString(2, ticket.getHallName());
            pstmt.setString(3, ticket.getShowDate());
            pstmt.setString(4, ticket.getStartTime());
            pstmt.setFloat(5,ticket.getPrice());
            pstmt.setString(6,ticket.getTicketSeat());
            pstmt.setInt(7,ticket.getFilmId());
            pstmt.setInt(8,ticket.getShowId());

            r= pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }
        return r;
    }

    @Override
    public List<FilmIdVo> selsect() {
        List<FilmIdVo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select top 10 film_id from ticketInfo group by film_id order by COUNT(1) desc";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            rst=pstmt.executeQuery();
            while (rst.next()){
                int id=rst.getInt(1);
                FilmIdVo vo=new FilmIdVo(id);
                list.add(vo);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }

    @Override
    public int selectCountById(int filmId) {
        int r=0;

        Connection conn=DBUtil.getConnection();
        String sql="select COUNT(*) as number from ticketInfo where film_id=?";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,filmId);
            rst= pstmt.executeQuery();
            if(rst.next()){
                r=rst.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return r;
    }

    @Override
    public List<TicketInfo> selectAll() {
        List<TicketInfo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from ticketInfo";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt= conn.prepareStatement(sql);
            rst= pstmt.executeQuery();
            while (rst.next()){
                int ticketId =rst.getInt(1);
                String filmName = rst.getString(2);
                String hallName = rst.getString(3);
                String showDate = rst.getString(4);
                String startTime = rst.getString(5);
                Float price = rst.getFloat(6);
                String ticketSeat = rst.getString(7);
                int filmId = rst.getInt(8);
                int showId = rst.getInt(9);

                TicketInfo ticketInfo=new TicketInfo(ticketId,filmName,hallName,showDate,startTime,price,ticketSeat,filmId,showId);
                list.add(ticketInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return list;
    }

    @Override
    public int getSum() {
        int  sum=0;

        Connection conn=DBUtil.getConnection();
        String sql="select * from ticketInfo";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt= conn.prepareStatement(sql);
            rst= pstmt.executeQuery();
            while (rst.next()){

                Float price = rst.getFloat(6);
                sum= (int) (sum+price);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }


        return sum;
    }
}
